package com.dubbo.mapper;

import com.dubbo.domain.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;
import java.util.Map;

public interface UserMapper extends Mapper<User>{
	
	/**
	 * 根据手机号查询用户
	 *
	 * @author lihongwu
	 * 2019年1月3日 上午9:47:33
	 * @param phone
	 * @return
	 */
	@Select("SELECT" + 
			"	*" + 
			" FROM" + 
			"	u_user u" + 
			" WHERE u.phone = #{phone}")
	User getUserByPhone(@Param("phone") String phone);
	
	/**
	 * 根据账号密码 查询用户
	 *
	 * @author lihongwu
	 * 2018年12月14日 上午11:34:47
	 * @param phone
	 * @param md5Pws
	 * @return
	 */
	@Select("SELECT" + 
			"	u.id,u.avatarUrl,u.nick_name" + 
			" FROM" + 
			"	u_user u" + 
			" WHERE" + 
			"	u.phone = #{phone}" + 
			" AND u.`password` = #{md5Pws}")
	User selectByPhoneAndPwd(@Param("phone") String phone, @Param("md5Pws") String md5Pws);
	
	/**
	 * 获取用户个人资料
	 *
	 * @author lihongwu
	 * 2018年12月14日 上午11:05:02
	 * @param userId
	 * @return
	 */
	@Select("SELECT" + 
			"	u.id," + 
			"	u.avatarUrl," + 
			"	u.nick_name," + 
			"	u.country AS 'province'," + 
			"	u.province AS 'city'," + 
			"	u.city AS 'area'" + 
			" FROM" + 
			"	u_user u " + 
			" WHERE u.id = #{userId}")
	Map<String,Object> getUserInformation(@Param("userId") String userId);
	
	/**
	 * 检验数据库是否已存在手机号   
	 *
	 * @author lihongwu
	 * 2018年12月14日 下午12:00:23
	 * @param phone
	 * @return ture=已存在  false = 不存在
	 */
	@Select("SELECT" + 
			"	IFNULL(COUNT(u.id),0)" + 
			" FROM" + 
			"	u_user u" + 
			" WHERE u.phone = #{phone}")
	boolean checkPhoneExist(@Param("phone") String phone);
	
	
	/**
	 * 我的电工
	 * @author gujiahua
	 * 2018年12月17日 上午11:12:44
	 * @param pageStart
	 * @param pageTotal
	 * @param keyWord
	 * @return
	 */
	@Select("SELECT " + 
			"	us.id AS userId, " + 
			"	us.avatarUrl, " + 
			"	us.phone, " + 
			"	us.nick_name, " + 
			"	us.country AS 'province', " + 
			"	us.province AS 'city', " + 
			"	us.city AS 'area' " + 
			"FROM " + 
			"	u_role_level url " + 
			"JOIN u_user us ON us.id = url.electricId " + 
			"WHERE " + 
			"	us.type = 3 " + 
			"AND ( " + 
			"	(url.agentId = #{userId} AND url.traderId IS NULL) " + 
			"	OR url.traderId = #{userId} " + 
			") " + 
			"AND ( " + 
			"	us.nick_name LIKE CONCAT('%', #{keyWord}, '%') " + 
			"	OR us.phone LIKE CONCAT('%', #{keyWord}, '%') " + 
			") " + 
			"GROUP BY " + 
			"	us.id " + 
			"LIMIT #{pageStart},#{pageTotal} ")
	List<Map<String,Object>> getElectricians(@Param("userId") String userId,
                                             @Param("pageStart") Long pageStart
            , @Param("pageTotal") Long pageTotal
            , @Param("keyWord") String keyWord);
	
	/**
	 * 我的电工总数
	 * @author gujiahua
	 * 2018年12月18日 下午2:20:12
	 * @param userId
	 * @param keyWord
	 * @return
	 */
	@Select("SELECT " + 
			"	COUNT(DISTINCT us.id) " + 
			"FROM " + 
			"	u_role_level url " + 
			"JOIN u_user us ON us.id = url.electricId " + 
			"WHERE " + 
			"	us.type = 3 " + 
			"AND ( " + 
			"	(url.agentId = #{userId} AND url.traderId IS NULL) " + 
			"	OR url.traderId = #{userId} " + 
			") " + 
			"AND ( " + 
			"	us.nick_name LIKE CONCAT('%', #{keyWord}, '%') " + 
			"	OR us.phone LIKE CONCAT('%', #{keyWord}, '%') " + 
			") ")
	Long getElectriciansCount(@Param("userId") String userId
            , @Param("keyWord") String keyWord);
	
	/**
	 * 我的分销商
	 * @author gujiahua
	 * 2018年12月17日 上午11:12:44
	 * @param pageStart
	 * @param pageTotal
	 * @param keyWord
	 * @return
	 */
	@Select("SELECT " + 
			"	us.id AS userId, " + 
			"	us.avatarUrl, " + 
			"	us.phone, " + 
			"	us.nick_name, " + 
			"	us.country AS 'province', " + 
			"	us.province AS 'city', " + 
			"	us.city AS 'area' " + 
			"FROM " + 
			"	u_role_level url " + 
			"JOIN u_user us ON us.id = url.traderId " + 
			"WHERE " + 
			"	us.type = 2 " + 
			"AND  " + 
			"	url.agentId = #{userId} " + 
			"AND ( " + 
			"	us.nick_name LIKE CONCAT('%', #{keyWord}, '%') " + 
			"	OR us.phone LIKE CONCAT('%', #{keyWord}, '%') " + 
			") " + 
			"GROUP BY " + 
			"	us.id " + 
			"LIMIT #{pageStart},#{pageTotal}")
	List<Map<String,Object>> getDistributors(@Param("userId") String userId,
                                             @Param("pageStart") Long pageStart
            , @Param("pageTotal") Long pageTotal
            , @Param("keyWord") String keyWord);
	
	/**
	 * 我的分销商总数
	 * @author gujiahua
	 * 2018年12月18日 下午2:18:13
	 * @param userId
	 * @param keyWord
	 * @return
	 */
	@Select("SELECT " + 
			"	COUNT(DISTINCT us.id) " + 
			"FROM " + 
			"	u_role_level url " + 
			"JOIN u_user us ON us.id = url.traderId " + 
			"WHERE " + 
			"	us.type = 2 " + 
			"AND  " + 
			"	url.agentId = #{userId} " + 
			"AND ( " + 
			"	us.nick_name LIKE CONCAT('%', #{keyWord}, '%') " + 
			"	OR us.phone LIKE CONCAT('%', #{keyWord}, '%') " + 
			")")
	Long getDistributorsCount(@Param("userId") String userId,
                              @Param("keyWord") String keyWord);
	
	/**
	 * 获取个人信息
	 * @author gujiahua
	 * 2018年12月18日 上午11:17:26
	 * @param userId
	 * @return
	 */
	@Select("SELECT " + 
			"	us.nick_name, " + 
			"	us.avatarUrl, " + 
			"	us.phone, " + 
			"	us.country AS 'province'," + 
			"	us.province AS 'city'," + 
			"	us.city AS 'area'," + 
			" 	us.shopName, " +
			" 	us.shopAddress, " +
			"	( " + 
			"		CASE us.type " + 
			"		WHEN 0 THEN " + 
			"			'user' " + 
			"		WHEN 1 THEN " + 
			"			'agent' " + 
			"		WHEN 2 THEN " + 
			"			'trader' " + 
			"		WHEN 3 THEN " + 
			"			'electric' " + 
			"		END " + 
			"	) AS role, " + 
			"	( " + 
			"		CASE us.sex " + 
			"		WHEN 0 THEN " + 
			"			'女' " + 
			"		WHEN 1 THEN " + 
			"			'男' " + 
			"		END " + 
			"	) AS sex " + 
			"FROM " + 
			"	u_user us " + 
			"WHERE us.id =#{userId} LIMIT 1")
	Map<String,Object> getUserInformations(@Param("userId") String userId);
	
	/**
	 * 获取用户消息
	 * 
	 * @author gujiahua
	 * 2018年12月18日 上午11:53:19
	 * @param userId
	 * @return
	 */
	@Select("SELECT " + 
			"	msg.id AS msgId, " + 
			"	msg.title, " + 
			"	msg.content, " + 
			"	msg.create_time, " + 
			"	( " + 
			"		CASE msg.type " + 
			"		WHEN 0 THEN " + 
			"			'系统通知' " + 
			"		WHEN 1 THEN " + 
			"			'安装补助金通知' " + 
			"		WHEN 2 THEN " + 
			"			'订单通知' " + 
			"		END " + 
			"	) AS type " + 
			"FROM u_message msg " + 
			"WHERE msg.userId =#{userId} "+
			"ORDER BY msg.create_time DESC "+
			"LIMIT #{pageStart},#{pageTotal}")
	List<Map<String, Object>> getMessageList(@Param("userId") String userId
            , @Param("pageStart") Long pageStart
            , @Param("pageTotal") Long pageTotal);

	/**
	 * 获取电工
	 * @author wujunyuan
	 * @date 2018年12月19日  下午2:47:59
	 * @param id
	 * @return
	 */
	@Select("SELECT" + 
			"	id AS userId," + 
			"	nick_name," + 
			"	phone" + 
			" FROM" + 
			"	u_user uu" + 
			" WHERE uu.`level`=3 AND uu.user_parent_id = #{userId}")
	List<Map<String, Object>> findElectriciansList(@Param("userId") String userid);
	
	/**
	 * 根据用户id获取用户信息 
	 * @author chenluqiang
	 * @date 2019年1月2日 下午4:54:13 
	 * @param userId
	 * @return
	 */
	@Select("SELECT usr.id AS userId,usr.nick_name AS 'userName',usr.phone FROM u_user usr WHERE usr.id = #{userId}")
	Map<String,Object> getUserMsgByUserId(@Param("userId") String userId);

	/**
	 * 根据sysUserId获取客服电话
	 * @author wujunyuan
	 * @date 2019年1月3日  上午11:30:38
	 * @param sysUserId
	 * @return
	 */
	@Select("SELECT sur.servicephone FROM sys_user sur WHERE sur.id = #{sysUserId}")
	Map<String, Object> getServicePhone(@Param("sysUserId") String sysUserId);
	
	/**
	 * 根据订单id查询用户
	 *
	 * @author lihongwu
	 * 2019年3月1日 上午11:50:38
	 * @param orderId
	 * @return
	 */
	@Select("SELECT " + 
			"	u.* " + 
			"FROM " + 
			"	u_user u " + 
			"LEFT JOIN o_order o ON u.id = o.userId " + 
			"WHERE o.id = #{orderId}")
	User getUserByOrderId(@Param("orderId") String orderId);
	
	/**
	 * 根据用户id获取用户信息
	 * @author chenluqiang
	 * @date 2019年4月1日 下午3:29:15 
	 * @param userId
	 * @return
	 */
	@Select("SELECT " + 
			"	us.nick_name, " + 
			"	us.avatarUrl, " + 
			"	us.phone, " + 
			"	us.country AS 'province'," + 
			"	us.province AS 'city'," + 
			"	us.city AS 'area'," + 
			" 	us.shopName, " +
			" 	(CASE us.type WHEN 0 THEN '普通用户' WHEN 1 THEN '代理商' WHEN 2 THEN '分销商' WHEN 3 THEN '电工'END)AS userType," +
			" 	us.shopAddress " +
			"FROM " + 
			"	u_user us " + 
			"WHERE us.id =#{userId} LIMIT 1")
	Map<String,Object> getInfoById(@Param("userId") String userId);
}